package com.xoa.dev.pz.service.impl;

import com.xoa.dao.common.SysCodeMapper;
import com.xoa.dao.party.GridInfoMapper;
import com.xoa.dev.pz.dao.MenuJdtMapper;
import com.xoa.dev.pz.dao.PzPersonMapper;
import com.xoa.dev.pz.dao.PzFullReleaseMapper;
import com.xoa.model.common.SysCode;
import com.xoa.model.enclosure.Attachment;
import com.xoa.model.party.GridInfo;
import com.xoa.dev.pz.model.PzPersonWithBLOBs;
import com.xoa.dev.pz.model.PzFullRelease;
import com.xoa.dev.pz.model.PzFullReleaseWithBLOBs;
import com.xoa.model.users.Users;
import com.xoa.dev.pz.service.PzFullReleaseService;
import com.xoa.service.sys.SysLogService;
import com.xoa.util.DateFormat;
import com.xoa.util.ExcelUtil;
import com.xoa.util.GetAttachmentListUtil;
import com.xoa.util.ToJson;
import com.xoa.util.common.StringUtils;
import com.xoa.util.common.log.FileUtils;
import com.xoa.util.common.session.SessionUtils;
import com.xoa.util.netdisk.CheckAll;
import com.xoa.util.page.PageParams;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by gsb on 2017/11/29.
 */

@Service
public class PzFullReleaseServiceImpl implements PzFullReleaseService {

    @Resource
    PzFullReleaseMapper pzFullReleaseMapper;

    @Autowired
    PzPersonMapper pzPersonMapper;
    @Autowired
    SysLogService sysLogService;
    @Autowired
    SysCodeMapper sysCodeMapper;
    @Autowired
    GridInfoMapper gridInfoMapper;
    @Autowired
    private MenuJdtMapper menuJdtMapper;


    @Transactional
    public ToJson insertFullRelease(HttpServletRequest request, PzFullReleaseWithBLOBs pzFullRelease) {
        ToJson<PzFullRelease> toJson = new ToJson<PzFullRelease>(1, "error");
        try {
            Users users = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users());
            pzFullRelease.setCreateUser(users.getUserId());
            pzFullRelease.setCreateTime(DateFormat.getDatestr(new Date()));
            if(pzFullRelease.getpWheRecidivism()==null){
                pzFullRelease.setpWheRecidivism("0");
            }
            if(pzFullRelease.getpWheAlrPlace()==null){
                pzFullRelease.setpWheAlrPlace("0");
            }
            if(pzFullRelease.getpWheDetached()==null){
                pzFullRelease.setpWheDetached("0");
            }
            pzFullRelease.setCreateTime(DateFormat.getCurrentTime());
            int temp = pzFullReleaseMapper.insertSelective(pzFullRelease);
            sysLogService.setPzLog(request,users.getUserId(),new Date(),"1","1","刑满释放人员");
            if (temp > 0) {
                toJson.setFlag(0);
                toJson.setMsg("success");
            }
        } catch (Exception e) {
            toJson.setFlag(1);
            toJson.setMsg(e.getMessage());
            e.printStackTrace();
        }

        return toJson;
    }

    @Transactional
    public ToJson delFullRelease(HttpServletRequest request, int pid) {
        ToJson toJson = new ToJson(1, "error");
        try {
            Users users = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users());
            int temp = pzFullReleaseMapper.deleteByPrimaryKey(pid);
            sysLogService.setPzLog(request,users.getUserId(),new Date(),"2","1","刑满释放人员");
            if (temp > 0) {
                toJson.setFlag(0);
                toJson.setMsg("success");
            }
        } catch (Exception e) {
            toJson.setFlag(1);
            toJson.setMsg(e.getMessage());
            e.printStackTrace();
        }
        return toJson;
    }

    @Transactional
    public ToJson upFullRelease(HttpServletRequest request, PzFullReleaseWithBLOBs PzFullRelease) {
        ToJson toJson = new ToJson<>(1, "error");
        try {
            Users users = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users());
            if(PzFullRelease.getpWheRecidivism()==null){
                PzFullRelease.setpWheRecidivism("0");
            }
            if(PzFullRelease.getpWheAlrPlace()==null){
                PzFullRelease.setpWheAlrPlace("0");
            }
            if(PzFullRelease.getpWheDetached()==null){
                PzFullRelease.setpWheDetached("0");
            }
            int temp = pzFullReleaseMapper.updateByPrimaryKeySelective(PzFullRelease);
            sysLogService.setPzLog(request,users.getUserId(),new Date(),"3","1","刑满释放人员");
            if (temp > 0) {
                toJson.setFlag(0);
                toJson.setMsg("success");
            }
        } catch (Exception e) {
            toJson.setFlag(1);
            toJson.setMsg(e.getMessage());
            e.printStackTrace();
        }
        return toJson;

    }

    @Transactional
    public ToJson selectFullRelease(HttpServletRequest request, HttpServletResponse response, Integer page, Integer pageSize, boolean useFlag, PzPersonWithBLOBs pzPersonWithBLOBs, String export, String pCradId, String pName) {
        ToJson<PzFullReleaseWithBLOBs> toJson = new ToJson<>(1, "error");
            PageParams pageParams = new PageParams();
            pageParams.setPage(page);
            pageParams.setPageSize(pageSize);
            pageParams.setUseFlag(useFlag);
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("page", pageParams);
            map.put("pCradId", pCradId);
            map.put("pName", pName);
            Map<String, Object> param = new HashMap<String, Object>();
            Users user= SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users());
            param.put("userId", user.getUserId());
            param.put("userPriv", user.getUserPriv());
            param.put("deptId", user.getDeptId());
            String menuModel=menuJdtMapper.selMenuByModel("4");
            if(!CheckAll.checkAll(menuModel,param)){
                pzPersonWithBLOBs.setCreateUser(user.getUserId());
            }
            map.put("pzPersonWithBLOBs", pzPersonWithBLOBs);
            try {
                if(export==null){
                    export="0";
                }
                List<PzFullReleaseWithBLOBs> list = pzFullReleaseMapper.selectPzFullReleaseWithBLOBs(map);
                for(PzFullReleaseWithBLOBs pzPersonWithBLOBs1:list){
                    SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd");

                    if ("0".equals(pzPersonWithBLOBs1.getpSex())) {
                        pzPersonWithBLOBs1.setpSexStr("男");
                    } else {
                        pzPersonWithBLOBs1.setpSexStr("女");
                    }
                    if(pzPersonWithBLOBs1.getpDate()!=null){
                        pzPersonWithBLOBs1.setpDateStr(sdf.format(pzPersonWithBLOBs1.getpDate()));
                    }

                    SysCode sysCode = sysCodeMapper.getSingleCode("RENT_CONCERN", pzPersonWithBLOBs1.getpFwiLevel());
                    if(sysCode!=null){
                        pzPersonWithBLOBs1.setpFwiLevel(sysCode.getCodeName());
                    }
                    SysCode sysCode1 = sysCodeMapper.getSingleCode("P_EDU_SITUATION", pzPersonWithBLOBs1.getpEduSituation());
                    if(sysCode1!=null){
                        pzPersonWithBLOBs1.setpEduSituation(sysCode1.getCodeName());
                    }
                    if ("01".equals(pzPersonWithBLOBs1.getpMaritalStatus())) {
                        pzPersonWithBLOBs1.setpMaritalStatusStr("未婚");
                    } else {
                        pzPersonWithBLOBs1.setpMaritalStatusStr("已婚");
                    }
                    if ("0".equals(pzPersonWithBLOBs1.getpVehicles())) {
                        pzPersonWithBLOBs1.setpVehiclesStr("无");
                    } else {
                        pzPersonWithBLOBs1.setpVehiclesStr("有");
                    }
                    SysCode sysCode2 = sysCodeMapper.getSingleCode("P_CRIME_TYPE", pzPersonWithBLOBs1.getpCrimeType());
                    if(sysCode2!=null){
                        pzPersonWithBLOBs1.setpCrimeType(sysCode2.getCodeName());
                    }
                    SysCode sysCode3 = sysCodeMapper.getSingleCode("P_COHESION", pzPersonWithBLOBs1.getpCohesion());
                    if(sysCode3!=null){
                        pzPersonWithBLOBs1.setpCohesion(sysCode3.getCodeName());
                    }
                    SysCode sysCode4 = sysCodeMapper.getSingleCode("P_RISK_ASSESSMENT", pzPersonWithBLOBs1.getpRiskAssessment());
                    if(sysCode4!=null){
                        pzPersonWithBLOBs1.setpRiskAssessment(sysCode4.getCodeName());
                    }
                    SysCode sysCode5 = sysCodeMapper.getSingleCode("POLITICAL", pzPersonWithBLOBs1.getpPolitical());
                    if(sysCode5!=null){
                        pzPersonWithBLOBs1.setpPolitical(sysCode5.getCodeName());
                    }
                    SysCode sysCode6 = sysCodeMapper.getSingleCode("DEGREE", pzPersonWithBLOBs1.getpEducation());
                    if(sysCode6!=null){
                        pzPersonWithBLOBs1.setpEducation(sysCode6.getCodeName());
                    }
                    if(pzPersonWithBLOBs1.getpOutPrisonTime()!=null){
                        String outTime = sdf.format(pzPersonWithBLOBs1.getpOutPrisonTime());
                        pzPersonWithBLOBs1.setOutTime(outTime);
                    }
                    if ("0".equals(pzPersonWithBLOBs1.getpWheRecidivism())) {
                        pzPersonWithBLOBs1.setpWheRecidivismStr("否");
                    } else {
                        pzPersonWithBLOBs1.setpWheRecidivismStr("是");
                    }
                    if ("0".equals(pzPersonWithBLOBs1.getpWheAlrPlace())) {
                        pzPersonWithBLOBs1.setpWheAlrPlaceStr("否");
                    } else {
                        pzPersonWithBLOBs1.setpWheAlrPlaceStr("是");
                    }
                    if(pzPersonWithBLOBs1.getpJoinDate()!=null){
                        String joinDate = sdf.format(pzPersonWithBLOBs1.getpJoinDate());
                        pzPersonWithBLOBs1.setJoinDate(joinDate);
                    }
                    if ("1".equals(pzPersonWithBLOBs1.getpWheTyearRepeat())) {
                        pzPersonWithBLOBs1.setpWheTyearRepeatStr("是");
                    } else {
                        pzPersonWithBLOBs1.setpWheTyearRepeatStr("否");
                    }
                    if ("0".equals(pzPersonWithBLOBs1.getpWheDetached())) {
                        pzPersonWithBLOBs1.setpWheDetachedStr("否");
                    } else {
                        pzPersonWithBLOBs1.setpWheDetachedStr("是");
                    }
                }
                toJson.setObj(list);
                toJson.setTotleNum(pageParams.getTotal());
                toJson.setMsg("success");
                toJson.setFlag(0);


                if(export.equals("1")){
                    HSSFWorkbook workbook1 = ExcelUtil.makeExcelHead("刑满释放人员信息导出", 38);
                    String[] secondTitles = {"所属网格","姓名","性别","身份证", "曾用名/别名", "出生日期","民族","联系方式","政治面貌","文化程度","婚姻状况","宗教信仰","职业","工作单位","户籍地址","籍贯","户籍地详址","现居地址","名下机动车辆","车牌号","备注","关注程度", "帮教情况", "犯罪类型", "原罪名", "原判刑期","服刑(劳教)场所","出监所时间","是否累犯","衔接情况","是否已安置","衔接日期","本年度是否重犯","是否脱管","犯罪行为","未安置原因","危险性评估"};
                    HSSFWorkbook workbook2 = ExcelUtil.makeSecondHead(workbook1, secondTitles);
                    String[] beanProperty = {"gridName","pName","pSexStr","pCradId","pAlias", "pDateStr","pNation","pPhone","pPolitical","pEducation","pMaritalStatusStr","pReligion","pOccupation","pWorkUnit","pDomicile","pOrigin","pDetailedDomicile","pAddress","pVehiclesStr","str1","pRemark", "pFwiLevel", "pEduSituation", "pCrimeType", "pOriSinName", "pImpTime", "pSerSentence","outTime","pWheRecidivismStr","pCohesion","pWheAlrPlaceStr","joinDate","pWheTyearRepeatStr","pWheDetachedStr","pCriminality","pResCause","pRiskAssessment"};
                    HSSFWorkbook workbook = null;
                    workbook = ExcelUtil.exportExcelData(workbook2, list, beanProperty);
                    OutputStream out = response.getOutputStream();
                    String filename = "刑满释放人员信息记录表.xls";
                    filename = FileUtils.encodeDownloadFilename(filename, request.getHeader("user-agent"));
                    response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                    response.setHeader("content-disposition", "attachment;filename=" + filename);
                    workbook.write(out);
                    out.close();
                }
            }catch (Exception e){
                e.printStackTrace();
                toJson.setMsg(e.getMessage());
            }
        return toJson;
    }

    @Transactional
    public ToJson selectFullrelEaseId(HttpServletRequest request, Integer pId) {
        ToJson<PzFullRelease> toJson = new ToJson<>(1, "error");
        try {
            PzFullReleaseWithBLOBs pzFullRelease = pzFullReleaseMapper.selectByPrimaryKey(pId);

            List<Attachment> attachmentList=new ArrayList<Attachment>();
            if(pzFullRelease.getAttachmentName()!=null&&!"".equals(pzFullRelease.getAttachmentName())){
                attachmentList= GetAttachmentListUtil.returnAttachment(pzFullRelease.getAttachmentName(),pzFullRelease.getAttachmentId(),"xoa" + (String) request.getSession().getAttribute("loginDateSouse"), GetAttachmentListUtil.MODULE_PZ_RELEASE);
            }
            pzFullRelease.setAttachmentList(attachmentList);

            SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd");
            String format = sdf.format(pzFullRelease.getpOutPrisonTime());
            pzFullRelease.setOutTime(format);
            String format1 = sdf.format(pzFullRelease.getpJoinDate());
            pzFullRelease.setJoinDate(format1);

            toJson.setObject(pzFullRelease);
            toJson.setFlag(0);
            toJson.setMsg("ok");
        } catch (Exception e) {

            toJson.setFlag(1);
            toJson.setMsg(e.getMessage());
            e.printStackTrace();
        }
        return toJson;

    }

    @Transactional
    public ToJson selectInfoById(HttpServletRequest request, Integer pId) {
        ToJson<PzFullRelease> toJson = new ToJson<>(1, "error");

        try {
            PzFullReleaseWithBLOBs pzFullRelease = pzFullReleaseMapper.selectInfoById(pId);
            SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd");
            String format = sdf.format(pzFullRelease.getpOutPrisonTime());
            pzFullRelease.setOutTime(format);
            SysCode sysCode = sysCodeMapper.getSingleCode("RENT_CONCERN", pzFullRelease.getpFwiLevel());
            if(sysCode!=null){
                pzFullRelease.setpFwiLevel(sysCode.getCodeName());
            }
            SysCode sysCode1 = sysCodeMapper.getSingleCode("P_EDU_SITUATION", pzFullRelease.getpEduSituation());
            if(sysCode1!=null){
                pzFullRelease.setpEduSituation(sysCode1.getCodeName());
            }
            SysCode sysCode2 = sysCodeMapper.getSingleCode("P_CRIME_TYPE", pzFullRelease.getpCrimeType());
            if(sysCode2!=null){
                pzFullRelease.setpCrimeType(sysCode2.getCodeName());
            }
            SysCode sysCode3 = sysCodeMapper.getSingleCode("P_COHESION", pzFullRelease.getpCohesion());
            if(sysCode3!=null){
                pzFullRelease.setpCohesion(sysCode3.getCodeName());
            }
            SysCode sysCode4 = sysCodeMapper.getSingleCode("P_RISK_ASSESSMENT", pzFullRelease.getpRiskAssessment());
            if(sysCode4!=null){
                pzFullRelease.setpRiskAssessment(sysCode4.getCodeName());
            }
            SysCode sysCode5 = sysCodeMapper.getSingleCode("POLITICAL", pzFullRelease.getpPolitical());
            if(sysCode5!=null){
                pzFullRelease.setpPolitical(sysCode5.getCodeName());
            }
            SysCode sysCode6 = sysCodeMapper.getSingleCode("DEGREE", pzFullRelease.getpEducation());
            if(sysCode6!=null){
                pzFullRelease.setpEducation(sysCode6.getCodeName());
            }
            String format1 = sdf.format(pzFullRelease.getpJoinDate());
            pzFullRelease.setJoinDate(format1);

            toJson.setObject(pzFullRelease);
            toJson.setFlag(0);
            toJson.setMsg("ok");
        } catch (Exception e) {

            toJson.setFlag(1);
            toJson.setMsg(e.getMessage());
            e.printStackTrace();
        }
        return toJson;

    }

    /**
     * 创建作者:  张丽军
     * 创建日期:   2017年12月7日 下午11:48
     * 方法介绍:   人员管理导入功能接口
     * @param file
     * @param response
     * @param request
     * @param session
     * @return
     */
    @Override
    public ToJson importFullRelease(MultipartFile file, HttpServletResponse response, HttpServletRequest request, HttpSession session) {
        ToJson<PzFullReleaseWithBLOBs> toJson = new ToJson<>(1, "error");
        Users users= SessionUtils.getSessionInfo(request.getSession(),Users.class,new Users());
        // 成功次数
        Integer successCount = 0;
        try {
            //判读当前系统
            //读取配置文件
            ResourceBundle rb = ResourceBundle.getBundle("upload");
            String osName = System.getProperty("os.name");
            StringBuffer path = new StringBuffer();
            if (osName.toLowerCase().startsWith("win")) {
                path = path.append(rb.getString("upload.win"));
            } else {
                path = path.append(rb.getString("upload.linux"));
            }
            // 判断是否为空文件
            if (file.isEmpty()) {
                toJson.setMsg("请上传文件！");
                toJson.setFlag(1);
                return toJson;
            } else {
                String fileName = file.getOriginalFilename();
                if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                    String uuid = UUID.randomUUID().toString().replaceAll("-", "");
                    int pos = fileName.indexOf(".");
                    String extName = fileName.substring(pos);
                    String newFileName = uuid + extName;
                    File dest = new File(path.toString(), newFileName);
//                    file.transferTo(dest);
                    // 将文件上传成功后进行读取文件
                    // 进行读取的路径
                   // String readPath = path.append(newFileName).toString();
                    String readPath = path.append(System.getProperty("file.separator")).append(newFileName).toString();
//                    InputStream in = new FileInputStream(readPath);
                    InputStream in =  file.getInputStream();
                    HSSFWorkbook excelObj = new HSSFWorkbook(in);
                    HSSFSheet sheetObj = excelObj.getSheetAt(0);
                    Row row = sheetObj.getRow(0);
                    int colNum = row.getPhysicalNumberOfCells();
                    int lastRowNum = sheetObj.getLastRowNum();
                    List<PzFullReleaseWithBLOBs> saveList = new ArrayList<PzFullReleaseWithBLOBs>();
                    List<PzPersonWithBLOBs> saveList1 = new ArrayList<PzPersonWithBLOBs>();
                    PzFullReleaseWithBLOBs pzFullReleaseWithBLOBs = null;
                    PzPersonWithBLOBs pzPersonWithBLOBs = null;
                    SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
                    int count = 0;
                    for (int i = 1; i <= lastRowNum; i++) {
                        pzFullReleaseWithBLOBs = new PzFullReleaseWithBLOBs();
                        pzPersonWithBLOBs = new PzPersonWithBLOBs();
                        row = sheetObj.getRow(i);
                        String value = "";
                        if (row != null) {
                            for (int j = 0; j < colNum; j++) {
                                Cell cell = row.getCell(j);
                                if (cell != null) {
                                    switch (j) {
                                        case 0:
                                            //所属网格
                                            String as=cell.getStringCellValue().trim();
                                            GridInfo info=gridInfoMapper.selectByUGridName(as);
                                            pzPersonWithBLOBs.setpGrid(info.getGridId());
                                            break;
                                        case 1:
                                            // 姓名
                                            pzPersonWithBLOBs.setpName(cell.getStringCellValue());
                                            break;
                                        case 2:
                                            // 身份证
                                            pzPersonWithBLOBs.setpCradId(cell.getStringCellValue());
                                            break;
                                        case 3:
                                            // 别名
                                            pzPersonWithBLOBs.setpAlias(cell.getStringCellValue());
                                            break;
                                        case 4:
                                            // 性别
                                            if("男".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpSex("0");
                                            }else if("女".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpSex("1");
                                            }
                                            break;
                                        case 5:
                                            // 出生日期
                                            pzPersonWithBLOBs.setpDate(sd.parse(cell.getStringCellValue().trim()));
                                            break;
                                        case 6:
                                            // 民族
                                            pzPersonWithBLOBs.setpNation(cell.getStringCellValue());
                                            break;
                                        case 7:
                                            // 联系方式
                                            if (cell.getCellType() == CellType.NUMERIC) {
                                                BigDecimal bigP=new BigDecimal(cell.getNumericCellValue());
                                                value = bigP.toString();
                                                //解决1234.0  去掉后面的.0
                                                if(null!=value&&!"".equals(value.trim())){
                                                    String[] item = value.split("[.]");
                                                    if(1<item.length&&"0".equals(item[1])){
                                                        value=item[0];
                                                    }
                                                }
                                                pzPersonWithBLOBs.setpPhone(value);
                                            }else {
                                                pzPersonWithBLOBs.setpPhone(cell.getStringCellValue());
                                            }
                                            break;
                                        case 8:
                                            // 政治面貌
                                            pzPersonWithBLOBs.setpPolitical((sysCodeMapper.getByName("POLITICAL",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 9:
                                            // 文化程度
                                            pzPersonWithBLOBs.setpEducation((sysCodeMapper.getByName("DEGREE",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 10:
                                            // 婚姻状况
                                            if("未婚".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpMaritalStatus("01");
                                            }else if("已婚".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpMaritalStatus("02");
                                            }
                                            break;
                                        case 11:
                                            //宗教信仰
                                            pzPersonWithBLOBs.setpReligion(cell.getStringCellValue());
                                            break;
                                        case 12:
                                            //职业
                                            pzPersonWithBLOBs.setpOccupation(cell.getStringCellValue());
                                            break;
                                        case 13:
                                            //工作单位
                                            pzPersonWithBLOBs.setpWorkUnit(cell.getStringCellValue());
                                            break;
                                        case 14:
                                            //户籍地址
                                            pzPersonWithBLOBs.setpDomicile(cell.getStringCellValue());
                                            break;
                                        case 15:
                                            //籍贯
                                            pzPersonWithBLOBs.setpOrigin(cell.getStringCellValue());
                                            break;
                                        case 16:
                                            //户籍地详址
                                            pzPersonWithBLOBs.setpDetailedDomicile(cell.getStringCellValue());
                                            break;
                                        case 17:
                                            //名下机动车辆
                                            if("无".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpVehicles("0");
                                            }else if("有".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpVehicles("1");
                                            }
                                            break;
                                        case 18:
                                            //备注
                                            pzPersonWithBLOBs.setpRemarks(cell.getStringCellValue());
                                            break;
                                        case 19:
                                            //有无固定住所
                                            if("无".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpIsHouse("0");
                                            }else if("有".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpIsHouse("1");
                                            }
                                            break;
                                        case 20:
                                            //现居地址
                                            pzPersonWithBLOBs.setpAddress(cell.getStringCellValue());
                                            break;
                                        case 21:
                                            //实口类型
                                            if("流动人口".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setFlag1("2");
                                            }else if("户籍人口".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setFlag1("1");
                                            }
                                            break;
                                        case 22:
                                            //车牌号
                                            cell.setCellType(CellType.STRING);
                                            pzPersonWithBLOBs.setStr1(cell.getStringCellValue());
                                            break;
                                        case 23:
                                            // 关注程度
                                            pzFullReleaseWithBLOBs.setpFwiLevel((sysCodeMapper.getByName("RENT_CONCERN",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 24:
                                            // 帮教情况
                                            pzFullReleaseWithBLOBs.setpEduSituation((sysCodeMapper.getByName("P_EDU_SITUATION",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 25:
                                            // 犯罪类型
                                            pzFullReleaseWithBLOBs.setpCrimeType((sysCodeMapper.getByName("P_CRIME_TYPE",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 26:
                                            // 原罪名
                                            pzFullReleaseWithBLOBs.setpOriSinName(cell.getStringCellValue());
                                            break;
                                        case 27:
                                            // 原判刑期
                                            pzFullReleaseWithBLOBs.setpImpTime(cell.getStringCellValue());
                                            break;
                                        case 28:
                                            // 服刑(劳教)场所
                                            pzFullReleaseWithBLOBs.setpSerSentence(cell.getStringCellValue());
                                            break;
                                        case 29:
                                            // 出监所时间
                                            pzFullReleaseWithBLOBs.setpOutPrisonTime(sd.parse(cell.getStringCellValue().trim()));
                                            break;
                                        case 30:
                                            // 是否累犯
                                            if("是".equals(cell.getStringCellValue().trim())){
                                                pzFullReleaseWithBLOBs.setpWheRecidivism("1");
                                            }else if("否".equals(cell.getStringCellValue().trim())){
                                                pzFullReleaseWithBLOBs.setpWheRecidivism("0");
                                            }
                                            break;
                                        case 31:
                                            //衔接情况
                                            pzFullReleaseWithBLOBs.setpCohesion((sysCodeMapper.getByName("P_COHESION",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 32:
                                            //是否已安置
                                            if("是".equals(cell.getStringCellValue().trim())){
                                                pzFullReleaseWithBLOBs.setpWheAlrPlace("1");
                                            }else if("否".equals(cell.getStringCellValue().trim())){
                                                pzFullReleaseWithBLOBs.setpWheAlrPlace("0");
                                            }
                                            break;
                                        case 33:
                                            //衔接日期
                                         //  Date datea = cell.getDateCellValue();
                                            pzFullReleaseWithBLOBs.setpJoinDate(sd.parse(cell.getStringCellValue().trim()));
                                            break;
                                        case 34:
                                            //本年度是否重犯
                                            if("是".equals(cell.getStringCellValue().trim())){
                                                pzFullReleaseWithBLOBs.setpWheTyearRepeat("1");
                                            }else if("否".equals(cell.getStringCellValue().trim())){
                                                pzFullReleaseWithBLOBs.setpWheTyearRepeat("2");
                                            }
                                            break;
                                        case 35:
                                            //是否脱管
                                            if("是".equals(cell.getStringCellValue().trim())){
                                                pzFullReleaseWithBLOBs.setpWheDetached("1");
                                            }else if("否".equals(cell.getStringCellValue().trim())){
                                                pzFullReleaseWithBLOBs.setpWheDetached("0");
                                            }
                                            break;
                                        case 36:
                                            //犯罪行为
                                            pzFullReleaseWithBLOBs.setpCriminality(cell.getStringCellValue());
                                            break;
                                        case 37:
                                            //未安置原因
                                            pzFullReleaseWithBLOBs.setpResCause(cell.getStringCellValue());
                                            break;
                                        case 38:
                                            //危险性评估
                                            pzFullReleaseWithBLOBs.setpRiskAssessment((sysCodeMapper.getByName("P_RISK_ASSESSMENT",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        default:
                                            toJson.setFlag(0);
                                            toJson.setMsg("err");
                                            break;
                                    }
                                }

                            }
                            List<String> nullArray=new ArrayList();
                            if(pzPersonWithBLOBs.getpGrid()==null){
                                nullArray.add("特殊网格");
                            }
                            if(pzPersonWithBLOBs.getpName()==null){
                                nullArray.add("姓名");
                            }
                            if(pzPersonWithBLOBs.getpSex()==null){
                                nullArray.add("性别");
                            }
                            if(pzPersonWithBLOBs.getpCradId()==null){
                                nullArray.add("身份证号");
                            }
                            if(pzPersonWithBLOBs.getFlag1()==null){
                                nullArray.add("实口类型");
                            }
                            if(pzPersonWithBLOBs.getpOrigin()==null){
                                nullArray.add("籍贯");
                            }
                            if(pzPersonWithBLOBs.getpIsHouse()==null){
                                nullArray.add("有无固定住所");
                            }
                            if(pzPersonWithBLOBs.getpAddress()==null){
                                nullArray.add("现居地址");
                            }
                            if(pzPersonWithBLOBs.getpMaritalStatus()==null){
                                nullArray.add("婚姻状况");
                            }
                            if(pzPersonWithBLOBs.getpDomicile()==null){
                                nullArray.add("户籍地址");
                            }
                            if(pzPersonWithBLOBs.getpDetailedDomicile()==null){
                                nullArray.add("户籍地详情");
                            }
                            if(pzPersonWithBLOBs.getpVehicles()==null){
                                nullArray.add("名下机动车辆");
                            }
                            if(pzFullReleaseWithBLOBs.getpImpTime()==null){
                                nullArray.add("原判刑期");
                            }
                            if(pzFullReleaseWithBLOBs.getpWheTyearRepeat()==null){
                                nullArray.add("本年度是否重犯");
                            }
                            if(pzFullReleaseWithBLOBs.getpOutPrisonTime()==null){
                                nullArray.add("出监所时间");
                            }
                            if(pzFullReleaseWithBLOBs.getpRiskAssessment()==null){
                                nullArray.add("危险性评估");
                            }
                            if(pzFullReleaseWithBLOBs.getpJoinDate()==null){
                                nullArray.add("衔接日期");
                            }
                            if(pzFullReleaseWithBLOBs.getpCohesion()==null){
                                nullArray.add("衔接情况");
                            }
                            if(pzFullReleaseWithBLOBs.getpCriminality()==null){
                                nullArray.add("犯罪行为");
                            }
                            if(pzFullReleaseWithBLOBs.getpResCause()==null){
                                nullArray.add("未安置的原因");
                            }
                            if(nullArray.size()>0){
                                toJson.setObject(nullArray);
                                toJson.setMsg("必填字段不能为空，请重新导入");
                            }else {
                                //判断用户是否为空，不为空判断用户是否存在
                                if (!StringUtils.checkNull(pzPersonWithBLOBs.getpName()) && !StringUtils.checkNull(pzPersonWithBLOBs.getpCradId())) {
                                    Map<String, Object> map = new HashMap<String, Object>();
                                    map.put("pCradId", pzPersonWithBLOBs.getpCradId());
                                    map.put("pName", pzPersonWithBLOBs.getpName());
                                    PzPersonWithBLOBs pzPersons = pzPersonMapper.selectPersonCountByNameCrad(map);
                                    if (pzPersons.getCount() > 0) {
                                        pzPersonWithBLOBs.setFlag2("0");
                                        pzPersonWithBLOBs.setpId(pzPersons.getpId());
                                        if(!pzPersons.getSpecialStr().contains("1,")){
                                            String str = "1," + pzPersons.getSpecialStr();
                                            pzPersonWithBLOBs.setSpecialStr(str);
                                        }
                                        pzPersonMapper.updateByPrimaryKeySelective(pzPersonWithBLOBs);
                                    } else {
                                        //创建用户和创建时间
                                        pzPersonWithBLOBs.setCreateUser(users.getUserId());
                                        pzPersonWithBLOBs.setCreateTime(DateFormat.getCurrentTime());
                                        pzPersonWithBLOBs.setSpecialStr("1,");
                                        pzPersonWithBLOBs.setFlag2("0");
                                        pzPersonMapper.insertSelective(pzPersonWithBLOBs);
                                    }
                                }
                                //将数据放入数据库
                                //用户表与表关联用户userid
                                pzFullReleaseWithBLOBs.setpUserId(pzPersonWithBLOBs.getpId());
                                //判断关联用户userid是否为空,为空新增,不为空修改
                                if(!StringUtils.checkNull(String.valueOf(pzFullReleaseWithBLOBs.getpUserId()))){
                                    //如果不为空，查询是否有信息，有则更新，否则新增
                                    Map<String, Object> map = new HashMap<String, Object>();
                                    map.put("userId", pzFullReleaseWithBLOBs.getpUserId());
                                    PzFullRelease pzFull=pzFullReleaseMapper.selectInfoBypUserId(map);
                                    if(pzFull.getCou()>0){
                                        pzFullReleaseWithBLOBs.setpId(pzFull.getpId());
                                        //pzFullReleaseWithBLOBs.setpUserId(pzFull.getpId());
                                        count +=pzFullReleaseMapper.updateByPrimaryKeySelective(pzFullReleaseWithBLOBs);
                                    }else {
                                        //创建用户和创建时间
                                        pzFullReleaseWithBLOBs.setCreateUser(users.getUserId());
                                        pzFullReleaseWithBLOBs.setCreateTime(DateFormat.getCurrentTime());
                                        count += pzFullReleaseMapper.insertSelective(pzFullReleaseWithBLOBs);
                                        pzFullReleaseWithBLOBs.setpUserId(pzPersonWithBLOBs.getpId());
                                    }
                                }
                            }
                        }
                    }
                    if(count>0) {
                        successCount = successCount + count;
                    }
                    dest.delete();
                    if(successCount>0){
                        toJson.setTotleNum(successCount);
                        toJson.setMsg("ok");
                        toJson.setFlag(0);
                        return  toJson;
                    }

                }
            }
        } catch (Exception e) {
            //读取配置文件,获取文件要存储的路径
            StringBuffer path = this.getPath();
            String fileName = file.getOriginalFilename();
            if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                String uuid = UUID.randomUUID().toString().replaceAll("-", "");
                String extName = fileName
                        .substring(fileName.lastIndexOf(".") + 1);// 文件后缀
                String newFileName = uuid + extName;//组合成新的文件名
                File dest = new File(path.toString(), newFileName);
                dest.delete();
            }
            e.getMessage();
            e.printStackTrace();
        }

        return toJson;
    }

    //判读当前系统,读取配置文件,返回路径
    public StringBuffer getPath(){
        ResourceBundle rb = ResourceBundle.getBundle("upload");
        String osName = System.getProperty("os.name");
        StringBuffer path = new StringBuffer();
        if (osName.toLowerCase().startsWith("win")) {
            path = path.append(rb.getString("upload.win"));
        } else {
            path = path.append(rb.getString("upload.linux"));
        }
        return path;
    }
}